package demo.jdbc;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ResourceUtils;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author : GuoFei
 * @date : 2019/3/7 10:10
 */
@Repository
public class MyDao {

    private static final Logger logger = LoggerFactory.getLogger(MyDao.class);

    @Data
    @AllArgsConstructor
    static class SqlServerConnInfo{
        String driver;
        String url;
        String username;
        String password;
    }


    @Value("${jdbc.driver}")
    private   String driver;
    @Value("${jdbc.url}")
    private   String url;
    @Value("${jdbc.username}")
    private   String username;
    @Value("${jdbc.password}")
    private   String password;

    public static SqlServerConnInfo info() throws IOException {
        Properties properties = new Properties();
        properties.load(new FileInputStream(ResourceUtils.getFile("classpath:properties/local/jdbc.properties")));
        return new SqlServerConnInfo(properties.getProperty("jdbc.driver"),properties.getProperty("jdbc.url"),properties.getProperty("jdbc.username"),properties.getProperty("jdbc.password"));
    }

    // 获取连接
    public Connection getConn() throws SQLException, IOException, ClassNotFoundException {
//        return test1();
        return test2();
    }

    // 通过properties 注解导入获取数据
    private Connection test1() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        return  DriverManager.getConnection(url,username,password);
    }
    // 通过读取properties文件获取数据
    private Connection test2() throws IOException, ClassNotFoundException, SQLException {
        SqlServerConnInfo connInfo = info();
        Class.forName(connInfo.getDriver());
        return DriverManager.getConnection(connInfo.getUrl(),connInfo.getUsername(),connInfo.getPassword());
    }



    @Transactional(readOnly = true,rollbackFor=RuntimeException.class)
    public int insert (String userName, String passWord , DataSource source) throws SQLException, IOException, ClassNotFoundException {
        Connection connection = DataSourceUtils.getConnection(source);

        String sql1 = "insert  into userTest (new_name,password) values (?,?)";
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
        preparedStatement1.setObject(1,"11111");
        preparedStatement1.setObject(2, passWord);
        int res = preparedStatement1.executeUpdate();
        if (true) {
            throw new RuntimeException("dfsdfsdf");
        }
        String sql = "insert  into userTest (new_name,password) values (?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,"222222");
        preparedStatement.setObject(2, passWord);
        res = preparedStatement.executeUpdate();
        return res;
    }

    public  JSONArray list() throws ClassNotFoundException, SQLException, IOException {
        Connection connection = getConn();
        String sql = "select top 10 iD,New_name,create_Time from userTest where id > ? order by id desc ; ";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, 2000);
        ResultSet resultSet = preparedStatement.executeQuery();
        JSONArray ja = new JSONArray();
        JSONObject jo;
        ResultSetMetaData data = resultSet.getMetaData();
        logger.info(JSON.toJSONString(data));
        for (int i = 0; i < data.getColumnCount(); i++) {
            System.out.println("1_"+data.getColumnLabel(i+1));
            System.out.println("2_"+data.getColumnName(i+1));
            System.out.println("3_"+data.getColumnType(i+1));
            System.out.println("4_"+data.getColumnTypeName(i+1));
            System.out.println("7_"+data.getColumnClassName(i+1));
            System.out.println("8_"+data.getColumnDisplaySize(i+1));
            System.out.println("9_"+data.getScale(i+1));
            System.out.println("_____------------");
        }
        while (resultSet.next()) {
            jo = new JSONObject();
            jo.put("id", resultSet.getInt("id"));
            jo.put("name", resultSet.getString("new_name"));
            jo.put("create_time1", resultSet.getString("create_time"));
            jo.put("create_time2", resultSet.getDate("create_time"));
            jo.put("create_time3", resultSet.getTime("create_time"));
            jo.put("create_time4", resultSet.getTimestamp("create_time"));
            ja.add(jo);
        }
        connection.close();
        resultSet.close();
        return ja;
    }

}
